import logging
import time
from typing import List
from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

admin_user_router = APIRouter(prefix="/sys/user", tags=["用户管理"])


class UserSearch(PaginationBase):
    sort: str = Field(default=None, description="排序字段")
    order: str = Field(default="desc", description="排序方式")
    username: str = Field(default=None, description="用户名")
    id: int = Field(default=None, description="用户id")
    fsiteNoBelong: str = Field(default=None, description="场地id")


# todo 9.用户资料
@admin_user_router.get("/list", summary="获取用户列表")
def admin_user_list(user: UserSearch = Query(), db: Session = Depends(get_db)):
    sql_3 = "select count(1)  from am_sys_user a"
    sql_3 = sql_3 + " left join a_ts_site b on b.fsite_no=a.fsite_no_belong"
    sql_3 = sql_3 + " where 1=1  "
    params = {}
    if user.username is not None:
        sql_3 = sql_3 + " and username like concat('%',:username,'%')"
        params['username'] = user.username
    if user.id is not None:
        sql_3 = sql_3 + " and id = :id"
        params['id'] = user.id
    if user.fsiteNoBelong is not None:
        sql_3 = sql_3 + " and fsite_no_belong = :fsiteNoBelong"
        params['fsiteNoBelong'] = user.fsiteNoBelong
    count = db.execute(text(sql_3), params).scalar()

    sql_3 = "select id,username,password,icon,email,nick_name,note,create_time,login_time,status,phone,create_id,"
    sql_3 = sql_3 + " update_time,update_id,fsite_no_belong,ftype,fpoint_noes,fcard,password_clear,fappno,enterprise_wechat "
    sql_3 = sql_3 + ",b.fsite_name,b.fsite_level,b.fsite_no"
    sql_3 = sql_3 + " from am_sys_user a"
    sql_3 = sql_3 + " left join a_ts_site b on b.fsite_no=a.fsite_no_belong"
    sql_3 = sql_3 + " where 1=1  "
    params = {}
    if user.username is not None:
        sql_3 = sql_3 + " and username like concat('%',:username,'%')"
        params['username'] = user.username
    if user.id is not None:
        sql_3 = sql_3 + " and id = :id"
        params['id'] = user.id
    if user.fsiteNoBelong is not None:
        sql_3 = sql_3 + " and fsite_no_belong = :fsiteNoBelong"
        params['fsiteNoBelong'] = user.fsiteNoBelong
    if user.sort is not None:
        sql_3 = sql_3 + " order by :sort  :order "
        params['sort'] = user.sort
        params['order'] = user.order
    sql_3 = sql_3 + " limit :limit offset :offset"
    params['limit'] = user.limit
    params['offset'] = user.offset
    data = db.execute(text(sql_3), params).fetchall()

    # role_list
    sql_4 = " select user_id,role_id from am_sys_user a,am_sys_user_role b where a.id=b.user_Id "
    params = {}
    if user.username is not None:
        sql_4 = sql_4 + " and username like concat('%',:username,'%')"
        params['username'] = user.username
    if user.fsiteNoBelong is not None:
        sql_4 = sql_4 + " and fsite_no_belong = :fsiteNoBelong"
        params['fsiteNoBelong'] = user.fsiteNoBelong
    sql_4 = sql_4 + " order by user_id,role_id"
    data_role = db.execute(text(sql_4), params).fetchall()
    data_list = []
    for j1 in range(len(data)):
        data_dict = {}
        data_dict["id"] = data[j1][0]
        data_dict["username"] = data[j1][1]
        data_dict["password"] = data[j1][2]
        data_dict["icon"] = data[j1][3]
        data_dict["email"] = data[j1][4]
        data_dict["nickName"] = data[j1][5]
        data_dict["note"] = data[j1][6]
        data_dict["createTime"] = data[j1][7]
        data_dict["loginTime"] = data[j1][8]
        data_dict["status"] = data[j1][9]
        data_dict["phone"] = data[j1][10]
        data_dict["createId"] = data[j1][11]
        data_dict["updateTime"] = data[j1][12]
        data_dict["updateId"] = data[j1][13]
        data_dict["fsiteNoBelong"] = data[j1][14]
        data_dict["ftype"] = data[j1][15]
        data_dict["fpointNoesVo"] = data[j1][16]
        data_dict["fcard"] = data[j1][17]
        data_dict["passwordClear"] = data[j1][18]
        data_dict["fappno"] = data[j1][19]
        data_dict["enterpriseWechat"] = data[j1][20]
        data_dict["fsiteName"] = data[j1][21]
        data_dict["fsiteLevel"] = data[j1][22]
        data_dict["fsiteNo"] = data[j1][23]
        data_dict["fusername"] = None
        data_dict["excludeAdmin"] = 0
        data_dict["froleid"] = 1
        user_id = data[j1][0]

        role_list = []
        for j2 in range(len(data_role)):
            if (str(data_role[j2][0]) == str(user_id)):
                role_list.append(data_role[j2][1])

        data_dict["roleIds"] = role_list
        data_list.append(data_dict)
    info_dict = {}
    info_dict['total'] = count
    info_dict['page'] = user.pageIndex
    info_dict['pages'] = (count + user.limit - 1) // user.limit
    info_dict['limit'] = user.pageSize
    info_dict['list'] = data_list
    return BaseResponse.success(data=info_dict)


@admin_user_router.post("/delete/{id}", summary="删除用户")
def delete_user(id: int = Path(description="用户id"), db: Session = Depends(get_db)):
    sql = "delete from am_sys_user where id= :id"
    db.execute(text(sql), {"id": id})
    sql = "delete from am_sys_user_role where user_id= :id"
    db.execute(text(sql), {"id": id})
    db.commit()
    return BaseResponse.success()


class UserCreate(BaseModel):
    id: int = Field(description="用户id")
    username: str = Field(description="用户名")
    fsiteNoBelong: str = Field(description="用户所属区域")
    phone: str = Field(description="用户手机号")
    email: str = Field(description="邮箱")
    status: int = Field(description="状态")

    ftype: int = Field(default=1, description="用户类型(2前端，1后端，3都是)")
    note: str = Field(default="", description="备注信息")
    fcard: str = Field(default="", description="身份证号")
    nickName: str = Field(default="", description="昵称")

    roleIds: List[int] = Field(default=[], description="用户角色")


@admin_user_router.post("/create", summary="新增用户")
def create_user(user: UserCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            user_id = EncryptionMiddleware.get_current_user(token)

            sql = "insert into am_sys_user(id,username,password,email,note,status,phone,fsite_no_belong,ftype,fcard,password_clear,create_time,create_id,update_time,update_id,nick_name)"
            sql = sql + " values(:id,:username,'1234',:email,:note,:status,:phone,:site_no,:ftype,:fcard,'1234',now(),:user_id,now(),:user_id,:nick_name);"

            i = db.execute(text(sql), {
                "id": user.id,
                "username": user.username,
                "email": user.email,
                "note": user.note,
                "status": user.status,
                "phone": user.phone,
                "site_no": user.fsiteNoBelong,
                "ftype": user.ftype,
                "fcard": user.fcard,
                "user_id": user_id,
                "nick_name": user.nickName
            })
            if user.roleIds:
                for key in range(len(user.roleIds)):
                    sql = "insert into am_sys_user_role(user_id,role_id) values( :user_id, :role_id);"
                    i = db.execute(text(sql), {
                        "user_id": user.id,
                        "role_id": user.roleIds[key]
                    })
                    if i is None:
                        return BaseResponse.error(message="新增用户角色信息失败")

            db.commit()
            current_datetime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            d1 = {}
            d1['id'] = user.id
            d1['username'] = user.username
            d1['password'] = "1234"
            d1['icon'] = None
            d1['email'] = user.email
            d1['nickname'] = user.nickName
            d1['note'] = user.note
            d1['createTime'] = current_datetime
            d1['loginTime'] = None
            d1['status'] = 1
            d1['phone'] = user.phone
            d1['createId'] = user_id
            d1['updateTime'] = current_datetime
            d1['updateId'] = user_id
            d1['fsiteNoBelong'] = user.fsiteNoBelong
            d1['ftype'] = user.ftype
            d1['fpointnoes'] = None
            d1['fcard'] = user.fcard
            d1['passwordClear'] = "1234"
            d1['excludeAdmin'] = 0
            d1['fpointNoes'] = None
            d1['fpointNoesVo'] = None
            d1['froleid'] = None
            d1['fsiteLevel'] = None
            d1['fsiteNo'] = None
            d1['froleid'] = None
            d1["roleIds"] = user.roleIds
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@admin_user_router.post("/update", summary="修改用户")
def update_user(user: UserCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    user_id = EncryptionMiddleware.get_current_user(token)

    # 修改用户信息
    sql = "update am_sys_user"
    sql = sql + " set username=:username ,email= :email ,note= :note ,phone= :phone ,fsite_no_belong= :fsite_no_belong ,ftype= :ftype "
    sql = sql + ",fcard= :fcard ,update_time=now(),update_id=:update_id ,nick_name= :nick_name"
    sql = sql + " where id= :user_id"

    i = db.execute(text(sql), {
        "username": user.username,
        "email": user.email,
        "note": user.note,
        "phone": user.phone,
        "fsite_no_belong": user.fsiteNoBelong,
        "ftype": user.ftype,
        "fcard": user.fcard,
        "update_id": user_id,
        "nick_name": user.nickName,
        "user_id": user.id
    })
    if i is None:
        return BaseResponse.error(message="修改用户信息失败")

    # 删除已有角色
    sql = "delete from am_sys_user_role where user_id= :user_id"
    i = db.execute(text(sql), {"user_id": user.id})
    if i is None:
        return BaseResponse.error(message="删除角色失败")

    # 新增角色
    if user.roleIds:
        for key in range(len(user.roleIds)):
            sql = "insert into am_sys_user_role(user_id,role_id)"
            sql = sql + " values(:user_id, :role_id );"
            i = db.execute(text(sql), {
                "user_id": user.id,
                "role_id": user.roleIds[key]
            })
            if i is None:
                return BaseResponse.error(message="修改角色信息失败")

    db.commit()
    return BaseResponse.success()
